﻿using Common.Logging;
using Quartz;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Wicresoft.UnifyShow.Business;
using Wicresoft.UnifyShow.BusinessImpl;
using Wicresoft.UnifyShow.Utility;

namespace Wicresoft.UnifyShow.PlanServices.Task
{
    public class CreateTask : Basic, IJob
    {
        string insertTable = @"
                insert into task(jobno,jobid,cycleid,taskname,TaskDescription,TaskDetail,status,location,jobtype,begindate,enddate,OwnerID,createuserid,updateuserid,createtime,updatetime,overflag,remark)  
                            ";

        public void Execute(IJobExecutionContext context)
        {
            var day = ConfigurationManager.AppSettings["AfterDay"];
            
            Logger.Log("start Create Task");
            string strSql = string.Empty;

            try
            {

                using (var trans = oda.BeginTransaction())
                {
                    try
                    {
                        //一次
                        strSql = insertTable + sqlCycleType1;
                        ModelHelper<TaskModel>.CreateModel_By_DirectSql(strSql, oda);
                        //每天
                        strSql = insertTable + sqlCycleType20;
                        ModelHelper<TaskModel>.CreateModel_By_DirectSql(strSql, oda);
                        //每天(周六日除外)
                        strSql = insertTable + sqlCycleType21;
                        ModelHelper<TaskModel>.CreateModel_By_DirectSql(strSql, oda);
                        //每周
                        strSql = insertTable + sqlCycleType3;
                        ModelHelper<TaskModel>.CreateModel_By_DirectSql(strSql, oda);
                        //每月
                        strSql = insertTable + sqlCycleType4;
                        ModelHelper<TaskModel>.CreateModel_By_DirectSql(strSql, oda);
                        //Update JobNo
                        ModelHelper<TaskModel>.CreateModel_By_DirectSql("Update Task Set Jobno='TK'||To_Char(Begindate,'YYYYMMDD')||Lpad(Rownum,6,'0') Where To_Char(Begindate,'YYYYMMDD')=To_Char(sysdate,'YYYYMMDD')", oda);
                        trans.Commit();
                    }
                    catch (Exception ex)
                    {
                        Logger.Error(strSql);
                        Logger.Error("Create Task", ex);
                        trans.Rollback();
                    }
                }
                try
                {
                    ModelHelper<TaskModel>.CreateModel_By_DirectSql(@"
                    update  Task set Overflag=1
                    Where Status=1 And Overflag=0  And  To_Char(Enddate+1,'YYYY/MM/DD')<= To_Char(Sysdate,'YYYY/MM/DD')", oda);
                }
                catch (Exception ex)
                {
                    Logger.Error("过期:", ex);
                }

                try
                {
                    strSql = string.Format(@"
                    update Task set status=4,updatetime=sysdate    Where Jobtype=1 And Status=2
                    and to_char(completetime+{0},'YYYYMMDD')<=to_char(sysdate,'YYYYMMDD')", day);
                    ModelHelper<TaskModel>.CreateModel_By_DirectSql(strSql, oda);
                }
                catch (Exception ex)
                {
                    Logger.Error(strSql);
                    Logger.Error("日常巡检任务，在已完成之后三天自动关闭:", ex);
                }
            }

            finally
            {
                oda.Dispose();
            }
        }

        #region sql
        #region sql  1
        ///一次
        string sqlCycleType1 = @"
                                    select 'TK'||To_Char(Begindate,'YYYYMMDD')||Lpad(8,6,'0') JobNo
                                    ,a.ID JobId,b.id CycLeId,a.jobname,a.JobDescription,a.JobDetail,1 Status
                                    ,case jobtype when 1 then (select id from sitebranch 
                                              where name =(select company from user_x where id=a.TaskUserID)) 
                                              else d.LocationID  end Location
                                            ,jobtype
                                    ,begindate
                                    ,enddate
                                    ,case jobtype when 3 then (select ownerid from planlocationowner plo where plo.locationid=d.locationid) else TaskUserID end   OwnerID
                                    ,JobOwnerID CreateUserID
                                    ,JobOwnerID UpdateUserID
                                    ,sysdate CreateTime
                                    ,sysdate UpdateTime
                                    ,0 OverFlag
                                    ,'' Remark
                                    from job_x a
                                    left join cycle b on a.id=b.jobid
                                 left join PlanTaskScope d on b.id=d.CycleID
                                   where b.PlanType=1 
                                    and to_char(b.begindate,'YYYY/MM/DD')=to_char(sysdate,'YYYY/MM/DD')
                                    and not exists (select * from task c where a.id=c.jobid and b.id=c.CycleID and to_char(c.begindate,'YYYY/MM/DD')=to_char(sysdate,'YYYY/MM/DD'))
                                ";
        #endregion
        #region sql  2
        ///每天  周六日有效
        string sqlCycleType20 = @"
                                    select 'TK'||To_Char(Begindate,'YYYYMMDD')||Lpad(8,6,'0') JobNo
                                    ,a.ID JobId,b.id CycLeId,a.jobname,a.JobDescription,a.JobDetail,1 Status
                                    ,case jobtype when 1 then (select id from sitebranch 
                                              where name =(select company from user_x where id=a.TaskUserID)) 
                                              else d.LocationID  end Location
                                            ,jobtype
                                    ,to_date(to_char(sysdate,'YYYY/MM/DD'),'YYYY/MM/DD')  begindate
                                    ,to_date(to_char(sysdate,'YYYY/MM/DD'),'YYYY/MM/DD') enddate
                                    ,case jobtype when 3 then (select ownerid from planlocationowner plo where plo.locationid=d.locationid) else TaskUserID end   OwnerID
                                    ,JobOwnerID CreateUserID
                                    ,JobOwnerID UpdateUserID
                                    ,sysdate CreateTime
                                    ,sysdate UpdateTime
                                    ,0 OverFlag
                                    ,'' Remark
                                    from job_x a
                                    left join cycle b on a.id=b.jobid
                                left join PlanTaskScope d on b.id=d.CycleID
                                    where b.PlanType=2 and a.Invalid=1 and HolidayInValid =0
                                and to_char(b.begindate,'YYYY/MM/DD')<=to_char(sysdate,'YYYY/MM/DD') 
                                and (to_char(b.EndDate,'YYYY/MM/DD')='0001/01/01' or to_char(b.EndDate,'YYYY/MM/DD')>=to_char(sysdate,'YYYY/MM/DD'))
                                    and not exists (select * from task c where a.id=c.jobid and b.id=c.CycleID and to_char(c.begindate,'YYYY/MM/DD')=to_char(sysdate,'YYYY/MM/DD'))
                                    ";
        ///每天  周六日排除
        string sqlCycleType21 = @"
                                    select 'TK'||To_Char(Begindate,'YYYYMMDD')||Lpad(8,6,'0') JobNo
                                    ,a.ID JobId,b.id CycLeId,a.jobname,a.JobDescription,a.JobDetail,1 Status
                                    ,case jobtype when 1 then (select id from sitebranch 
                                              where name =(select company from user_x where id=a.TaskUserID)) 
                                              else d.LocationID  end Location
                                            ,jobtype
                                    ,to_date(to_char(sysdate,'YYYY/MM/DD'),'YYYY/MM/DD')  begindate
                                    ,to_date(to_char(sysdate,'YYYY/MM/DD'),'YYYY/MM/DD') enddate
                                    ,case jobtype when 3 then (select ownerid from planlocationowner plo where plo.locationid=d.locationid) else TaskUserID end   OwnerID
                                    ,JobOwnerID CreateUserID
                                    ,JobOwnerID UpdateUserID
                                    ,sysdate CreateTime
                                    ,sysdate UpdateTime
                                    ,0 OverFlag
                                    ,'' Remark
                                    from job_x a
                                    left join cycle b on a.id=b.jobid
                                    left join PlanTaskScope d on b.id=d.CycleID
                                    where b.PlanType=2 and a.Invalid=1 and HolidayInValid =1 and to_char(sysdate - 1,'d') not in (6,7)
                                    and to_char(b.begindate,'YYYY/MM/DD')<=to_char(sysdate,'YYYY/MM/DD') 
                                    and (to_char(b.EndDate,'YYYY/MM/DD')='0001/01/01' or to_char(b.EndDate,'YYYY/MM/DD')>=to_char(sysdate,'YYYY/MM/DD'))
                                    and not exists (select * from task c where a.id=c.jobid and b.id=c.CycleID and to_char(c.begindate,'YYYY/MM/DD')=to_char(sysdate,'YYYY/MM/DD'))
                                    ";
        #endregion
        #region sql  3
        ///每周
        string sqlCycleType3 = @"
                                select 'TK'||To_Char(Begindate,'YYYYMMDD')||Lpad(8,6,'0') JobNo
                                    ,a.ID JobId,b.id CycLeId,a.jobname,a.JobDescription,a.JobDetail,1 Status
                                    ,case jobtype when 1 then (select id from sitebranch 
                                              where name =(select company from user_x where id=a.TaskUserID)) 
                                              else d.LocationID  end Location
                                            ,jobtype
                                ,to_date(to_char(sysdate,'YYYY/MM/DD'),'YYYY/MM/DD')  begindate
                                ,to_date(to_char(sysdate+b.length-1,'YYYY/MM/DD'),'YYYY/MM/DD') enddate
                                ,case jobtype when 3 then (select ownerid from planlocationowner plo where plo.locationid=d.locationid) else TaskUserID end   OwnerID
                                ,JobOwnerID CreateUserID
                                ,JobOwnerID UpdateUserID
                                ,sysdate CreateTime
                                ,sysdate UpdateTime
                                ,0 OverFlag
                                ,'' Remark
                                from job_x a
                                left join cycle b on a.id=b.jobid
                                 left join PlanTaskScope d on b.id=d.CycleID
                                where b.PlanType=3 and a.Invalid=1 and  to_char(sysdate - 1,'d')=b.day
                                and to_char(b.begindate,'YYYY/MM/DD')<=to_char(sysdate,'YYYY/MM/DD') 
                                and (to_char(b.EndDate,'YYYY/MM/DD')='0001/01/01' or to_char(b.EndDate,'YYYY/MM/DD')>=to_char(sysdate,'YYYY/MM/DD'))
                                and not exists (select * from task c where a.id=c.jobid and b.id=c.CycleID and to_char(c.begindate,'YYYY/MM/DD')=to_char(sysdate,'YYYY/MM/DD'))
                                ";
        #endregion
        #region sql  4
        ///每月
        string sqlCycleType4 = @"
                                select 'TK'||To_Char(Begindate,'YYYYMMDD')||Lpad(8,6,'0') JobNo
                                ,a.ID JobId,b.id CycLeId,a.jobname,a.JobDescription,a.JobDetail,1 Status
                                    ,case jobtype when 1 then (select id from sitebranch 
                                              where name =(select company from user_x where id=a.TaskUserID)) 
                                              else d.LocationID  end Location
                                            ,jobtype
                                ,to_date(to_char(sysdate,'YYYY/MM/DD'),'YYYY/MM/DD')  begindate
                                ,case when to_char(sysdate+b.length-1,'YYYY/MM/DD')>to_char(add_months(sysdate,1)-1,'YYYY/MM/DD') then add_months(sysdate,1)-1 
                                  else to_date(to_char(sysdate+b.length-1,'YYYY/MM/DD'),'YYYY/MM/DD') end enddate
                                ,case jobtype when 3 then (select ownerid from planlocationowner plo where plo.locationid=d.locationid) else TaskUserID end   OwnerID
                                ,JobOwnerID CreateUserID
                                ,JobOwnerID UpdateUserID
                                ,sysdate CreateTime
                                ,sysdate UpdateTime
                                ,0 OverFlag
                                ,'' Remark
                                from job_x a
                                left join cycle b on a.id=b.jobid
                                left join PlanTaskScope d on b.id=d.CycleID
                                Where B.Plantype=4 And A.Invalid=1 And Round(To_Char(Sysdate,'mm'))=B.Month
                                and  (round(to_char(sysdate,'dd'))=b.day or (b.day>=round(to_char(last_day(sysdate),'dd')) and to_char(sysdate,'dd')=to_char(last_day(sysdate),'dd')))
                                and to_char(b.begindate,'YYYY/MM/DD')<=to_char(sysdate,'YYYY/MM/DD') 
                                and (to_char(b.EndDate,'YYYY/MM/DD')='0001/01/01' or to_char(b.EndDate,'YYYY/MM/DD')>=to_char(sysdate,'YYYY/MM/DD'))
                                and not exists (select * from task c where a.id=c.jobid and b.id=c.CycleID and to_char(c.begindate,'YYYY/MM/DD')=to_char(sysdate,'YYYY/MM/DD'))
                                ";
        #endregion
        #endregion
    }
}
